package com.ftg.learn.jdbc.chapter01;

import java.nio.channels.Pipe;
import java.sql.*;
import java.util.*;

/**
 * 学习最简单的jdbc
 *
 * @author kangjx
 * @createTime 2022/9/21
 */
public class SimpleJdbc {

//    1、 驱动类
//
//    2 新建数据源 datasource
//    2.1 用户 名 root
//    2.2 密码   Love0913@kjx
//    2.3 地址 描述符  url : jdbc:mysql://localhost:3306?serverTimezone=UTC
//    3 jdbc
//    com.mysql.cj.jdbc.Driver  8+版本
//    com.mysql.jdbc.Driver;    5+
//    3.1 Connection : --> 连接 会话
//    3.2 statement   : jdbc用来执行静态sql语言的接口  年报
//    3.2.1 PreparedStatement  : 预编译 (N 比较快)
//    3.2.2 历史问题 --->
//    3.3 ResultSet    : 结果集 查询返回 的数据
//    3.4 ResultSetMetaData (元数据)  : 针对当前的ResultSet 返回对应的信息 ·表信息、列信息、类型，精度等「
//    3.5 预编译 statement 一个子类 PreparedStatment

    public static void main(String[] args) {
//        getSqlResultSet("select eno,ename,job,mgr from emp");
        //静态sql
//        getSqlResultSet2("select eno,ename,job,mgr from emp where eno = 7369");
//        getSqlResultSet3("select eno,ename,job,mgr from emp where eno > ? and ename like concat(?,'%')",7369,"S");
        getSqlResultSet4("select eno,ename,job,mgr from emp where eno > ? and ename like concat(?,'%')",7369,"S")
        .forEach(System.out::println);



    }

    private static String username = "root";
    private static String password = "Love0913@kjx";
    private static String url = "jdbc:mysql://localhost:3306/learn07?serverTimezone=UTC";

    /**
     * 返回值
     * 预编译sql
     *  select eno,ename,job,mgr from emp where eno = ?
     * @param sql
     */
    public static List<Map<String,Object>> getSqlResultSet4(String sql, Object... para){
        List<Map<String,Object>> list = new ArrayList<>();
        try(
                Connection conn = DriverManager.getConnection(url, username, password);
                PreparedStatement stmt =  conn.prepareStatement(sql);
        ) {
            //就是为预编译sql 赋值
            if(Objects.nonNull(para)){
                for (int i = 0; i < para.length; i++) {
                    stmt.setObject(i + 1 ,para[i]);
                }
            }
            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String,Object> map = new HashMap<>(5);
                for(int i = 0;i < rsmd.getColumnCount();i++){
                    map.put(rsmd.getColumnName(i + 1).toLowerCase(Locale.ROOT),rs.getObject(i+1));
                }
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 预编译sql
     *  select eno,ename,job,mgr from emp where eno = ?
     * @param sql
     */
    public static void getSqlResultSet3(String sql,Object... para){
        try(
                Connection conn = DriverManager.getConnection(url, username, password);
                PreparedStatement stmt =  conn.prepareStatement(sql);
        ) {
            //就是为预编译sql 赋值
            if(Objects.nonNull(para)){
                for (int i = 0; i < para.length; i++) {
                    stmt.setObject(i + 1 ,para[i]);
                }
            }

            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            while (rs.next()) {
                for(int i = 0;i < rsmd.getColumnCount();i++){
                    System.out.print(rsmd.getColumnName(i + 1).toLowerCase(Locale.ROOT)+"的值是:" + rs.getObject(i+1) +"\t");
                }
                System.out.println();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 带参数的问题
     * @param sql
     */
    public static void getSqlResultSet2(String sql){
        try(
                Connection conn = DriverManager.getConnection(url, username, password);
                Statement stmt =  conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
        ) {
            ResultSetMetaData rsmd = rs.getMetaData();

            while (rs.next()) {
                for(int i = 0;i < rsmd.getColumnCount();i++){
                    System.out.print(rsmd.getColumnName(i + 1).toLowerCase(Locale.ROOT)+"的值是:" + rs.getObject(i+1) +"\t");
                }
                System.out.println();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 为了解决 sql查询结果列数过多的情况
     * @param sql
     */
    public static void getSqlResultSet(String sql){
        try(
            Connection conn = DriverManager.getConnection(url, username, password);
            Statement stmt =  conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
        ) {
            ResultSetMetaData rsmd = rs.getMetaData();

            while (rs.next()) {
                for(int i = 0;i < rsmd.getColumnCount();i++){
                    System.out.print(rsmd.getColumnName(i + 1).toLowerCase(Locale.ROOT)+"的值是:" + rs.getObject(i+1) +"\t");
                }
                System.out.println();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 不能使用where的sql
     * 解决 finally里面的代码过多问题
     * @param sql  语句
     */
    public static void getSqlResultSet1(String sql){
        try(
            Connection conn = DriverManager.getConnection(url, username, password);
            Statement stmt =  conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
        ) {
            while (rs.next()) {
                Object eno = rs.getObject(1);
                Object ename = rs.getObject(2);
                Object job = rs.getObject(3);
                Object mgr = rs.getObject(4);
                System.out.printf("编号为:%d\t,用户名为%s\t,取务为%s\t\t,领导编号%d\t\n", eno, ename, job, mgr);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    /**
     * 讲解一下mysql8.0 和 5. 版本区别
     */
    public static void registerDriverCalss() {
        String username = "root";
        String password = "Love0913@kjx";
        String url = "jdbc:mysql://localhost:3306/learn07?serverTimezone=UTC";
        //1 引入驱动包  通过maven  library + -- 引入本地依赖
        //2 加载驱动类
//        2.1 [反射]  软件公司推荐给使用人员的
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
//       2.2  注册方法 DriverManger
        try {
            Driver d = new com.mysql.cj.jdbc.Driver();
            DriverManager.registerDriver(d);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

//        DriverManager 加载驱动类   里面提供了一些与连接相关的方法
        try {
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement();
            String sql = "select eno,ename,job,mgr,hirdate from emp";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int eno = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                System.out.printf("编号为:%d\t,用户名为%s\t,取务为%s\t\t,领导编号%d\t\n", eno, ename, job, mgr);
            }
        } catch (SQLException throwables) {
            System.out.println("没有连接上数据库");
            throwables.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}
